package cmpe273.proj1.dao;

import static cmpe273.proj1.dao.DAOUtil.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.UUID;

import cmpe273.proj1.model.Instructor;
import cmpe273.proj1.model.Student;

/**
 * This class represents a concrete JDBC implementation of the {@link UserDAO} interface.
 *
 * @author BalusC
 * @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
 */
public class UserDAOJDBC implements UserDAO {

    // Constants ----------------------------------------------------------------------------------

	private static final String SQL_INSERT_STUDENT =
			"INSERT INTO users (Username, Student_ID, PasswordHash) VALUES (?, ?, ?)";
	private static final String SQL_INSERT_INSTRUCTOR =
			"INSERT INTO users (Username, Instructor_ID, PasswordHash) VALUES (?, ?, ?)";
	private static final String SQL_PERMISSIONS = "SELECT Username, Student_ID, Instructor_ID FROM users WHERE SessionUUID = ?";
//	private static final String SQL_PERMISSIONS_LONG = "SELECT s.StudentId, i.InstructorId " +
//			"FROM users u " +
//			"LEFT JOIN student s ON s.Student_ID = u.Student_ID " +
//			"LEFT JOIN instructor i ON i.Instructor_ID = u.Instructor_ID " +
//			"WHERE u.SessionUUID = ?";
	private static final String SQL_LOGIN = "UPDATE users SET SessionUUID = ? WHERE Username = ? AND PasswordHash = ?";
	private static final String SQL_UPDATE_PASSWORD =
			"UPDATE users SET PasswordHash = ?, SessionUUID = '' WHERE Username = ?";
	private static final String SQL_LOGOUT =
			"UPDATE users SET SessionUUID = ? WHERE SessionUUID = ?";

	// Vars ---------------------------------------------------------------------------------------

    private DAOFactory daoFactory;

    // Constructors -------------------------------------------------------------------------------

    /**
     * Construct an User DAO for the given DAOFactory. Package private so that it can be constructed
     * inside the DAO package only.
     * @param daoFactory The DAOFactory to construct this User DAO for.
     */
    UserDAOJDBC(DAOFactory daoFactory) {
        this.daoFactory = daoFactory;
    }

    // Actions ------------------------------------------------------------------------------------
    
	@Override
	public void create(Student s, String password)
			throws IllegalArgumentException, DAOException {
		if (s.getId() == null) {
            throw new IllegalArgumentException("Student not yet created.  The Student_ID is NULL");
        }

        Object[] values = {
        		s.getStudentId(),
        		s.getId(),
        		DAOUtil.saltAndHash(s.getStudentId(), password)
        };

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = daoFactory.getConnection();
            preparedStatement = prepareStatement(connection, SQL_INSERT_STUDENT, false, values);
            int affectedRows = preparedStatement.executeUpdate();
            if (affectedRows == 0) {
                throw new DAOException("Creating user failed, no rows affected.");
            }
        } catch (SQLException e) {
            throw new DAOException(e);
        } finally {
            close(connection, preparedStatement);
        }
	}

	@Override
	public void create(Instructor i, String password)
			throws IllegalArgumentException, DAOException {
		if (i.getId() == null) {
            throw new IllegalArgumentException("Instructor not yet created.  The Instructor_ID is NULL");
        }

        Object[] values = {
        		i.getInstructorId(),
        		i.getId(),
        		DAOUtil.saltAndHash(i.getInstructorId(), password)
        };

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = daoFactory.getConnection();
            preparedStatement = prepareStatement(connection, SQL_INSERT_INSTRUCTOR, false, values);
            int affectedRows = preparedStatement.executeUpdate();
            if (affectedRows == 0) {
                throw new DAOException("Creating user failed, no rows affected.");
            }
        } catch (SQLException e) {
            throw new DAOException(e);
        } finally {
            close(connection, preparedStatement);
        }
	}
    
    @Override
	public String login(String username, String password) throws DAOException {
		Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sessionString = "";
        
        UUID sessionId = UUID.randomUUID();
        
        Object[] values = {
    			sessionId.toString(),
    			username,
    			DAOUtil.saltAndHash(username, password)
    	};
        
        try {
        	connection = daoFactory.getConnection();
        	preparedStatement = prepareStatement(connection, SQL_LOGIN, false, values);
        	int affectedRows = preparedStatement.executeUpdate();
        	if (affectedRows != 1) {
				throw new DAOException("Updating user failed, no rows affected.");
			}
        	sessionString = sessionId.toString();
        } catch (SQLException e) {
            throw new DAOException(e);
        } finally {
            close(connection, preparedStatement, resultSet);
        }

        return sessionString;
	}
    
    @Override
	public void updatePassword(String username, String password) throws DAOException {
		Object[] values = {
				DAOUtil.saltAndHash(username, password),
				username
		};

		Connection connection = null;
		PreparedStatement preparedStatement = null;

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_UPDATE_PASSWORD, false, values);
			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0) {
				throw new DAOException("Updating user failed, no rows affected.");
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement);
		}
	}

	@Override
	public String verifyPermissions(String sessionId) throws DAOException {
		Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String permissionString = "";
        
        Object[]  values = {
        		sessionId
        };

        try {
            connection = daoFactory.getConnection();
            preparedStatement = prepareStatement(connection, SQL_PERMISSIONS, false, values);
            resultSet = preparedStatement.executeQuery();
            
            if (resultSet.next()) {
            	if(resultSet.getString("Student_ID") != null && !resultSet.getString("Student_ID").isEmpty())
            		permissionString = "S" + resultSet.getString("Username");
            	else if(resultSet.getString("Instructor_ID") != null && !resultSet.getString("Instructor_ID").isEmpty())
            		permissionString = "I" + resultSet.getString("Username");
            	else if(resultSet.getString("Username").equals("admin"))
            		permissionString = "Admin";
            }
        } catch (SQLException e) {
            throw new DAOException(e);
        } finally {
            close(connection, preparedStatement, resultSet);
        }

        return permissionString;
	}

	@Override
	public void logout(String sessionId) throws DAOException {
		Object[] values = { 
    			sessionId
    	};

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = daoFactory.getConnection();
            preparedStatement = prepareStatement(connection, SQL_LOGOUT, false, values);
            int affectedRows = preparedStatement.executeUpdate();
            if (affectedRows == 0) {
                throw new DAOException("Logout failed, no rows affected.");
            }
        } catch (SQLException e) {
            throw new DAOException(e);
        } finally {
            close(connection, preparedStatement);
        }
	}
}